package com.raja.servingStation.dao;

import com.alibaba.fastjson.JSONObject;
import com.raja.servingStation.common.constant.Constants;
import com.raja.servingStation.common.constant.MysqlPageVo;
import com.raja.servingStation.datasources.RepairSql2oTemp;
import com.raja.servingStation.datasources.Sql2oConnection;
import com.raja.servingStation.entity.ServingStation;
import com.raja.servingStation.entity.vo.NoticeView;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.sql2o.Connection;
import org.sql2o.Query;

import java.util.List;

/**
 * Created by Administrator on 2017/9/30/030.
 */
@Repository
public class ServingStationDao {

    @Autowired
    private RepairSql2oTemp repairSql2oTemp;

    //发公文 待选择的接收方 回显到combo
    public List<ServingStation> queryStations() throws Exception {
        String sql = " SELECT ID, NAME, ABBNAME FROM T_SERVICING_STATION ";
        try (Connection con = repairSql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql);
            List<ServingStation> stations = query.setAutoDeriveColumnNames(true).executeAndFetch(ServingStation.class);
            return stations;
        }
    }





    /**
     * 维修站列表总数
     * @param paramMap
     * @return
     */
    public Long cntStationList(JSONObject paramMap) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(id) from T_SERVICING_STATION ");

//        if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//            sql.append(" and ( instr(o.ORDER_CODE, :keyword) > 0 or instr(o.CAR_NO, :keyword) > 0 or instr(o.DRIVER_PHONE, :keyword) > 0 ) ");
//        }
//        if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//            sql.append(" and ( o.ORDER_CODE = :keyword ");
//            sql.append(" or o.CAR_NO = :keyword ");
//            sql.append(" or o.DRIVER_PHONE = :keyword )");
//        }
//        log.debug(sql.toString());
        try (Connection con = repairSql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sql.toString());
//            if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//                query.addParameter("keyword", paramMap.get("keyWord"));
//            }
            return (Long)query.executeScalar();
        }
    }



    /** todo
     * 维修站列表
     * @param pageVO
     * @return
     * @throws Exception
     */
    public List<ServingStation> queryStationListByPage(JSONObject paramMap, MysqlPageVo pageVO) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from T_SERVICING_STATION ");

        if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
            sql.append(" and ( instr(o.ORDER_CODE, :keyword) > 0 or instr(o.CAR_NO, :keyword) > 0 or instr(o.DRIVER_PHONE, :keyword) > 0 ) ");
        }
//        if (!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
//            sql.append(" and ( o.ORDER_CODE = :keyword ");
//            sql.append(" or o.CAR_NO = :keyword ");
//            sql.append(" or o.DRIVER_PHONE = :keyword )");
//        }
//        sql = sql + " order by bb.WRITE_TIME desc";
//        sql.append(" order by o.UPDATE_TIME desc ");
        String sqlfy = Constants.mysql_page_pre_sql+sql.toString()+Constants.mysql_page_end_sql;
//        log.debug(sql.toString());
        try (Connection con = repairSql2oTemp.getSql2o().open()) {
            Query query = con.createQuery(sqlfy);
            if(!StringUtils.isEmpty(paramMap.getString("keyWord"))) {
                query.addParameter("keyword", paramMap.get("keyWord"));
            }
//            if(paramMap.get("state") != null) {
//                query.addParameter("state",paramMap.get("state"));
//            }
            query.addParameter("limit",pageVO.getPageSize().intValue());
            query.addParameter("offset",pageVO.getOffset().intValue());
            List<ServingStation> servingStations = query.setAutoDeriveColumnNames(true).executeAndFetch(ServingStation.class);
            return servingStations;
        }
    }







    public ServingStation getUserByLoginName(String loginName) throws Exception {
        String sql = "select ID, NAME  ,  PASSWORD,IS_FIRST_LOGIN from t_servicing_station where  ACCOUNT = :loginName ";
        try (Connection con = repairSql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("loginName",loginName);
            return query.setAutoDeriveColumnNames(true).executeAndFetchFirst(ServingStation.class);
        }
    }

    public ServingStation getUserByUserId(Integer userId) throws Exception {
        String sql = "select ID, NAME  ,  PASSWORD,IS_FIRST_LOGIN from t_servicing_station where  id = :userId ";
        try (Connection con = repairSql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("userId",userId);
            return query.setAutoDeriveColumnNames(true).executeAndFetchFirst(ServingStation.class);
        }
    }

    public void updateFirstPassword(ServingStation userInfo)throws Exception {
        String sql = "update t_servicing_station set password =:password where  id = :userId ";
        try (Connection con = repairSql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("userId",userInfo.getId());
            query.addParameter("password",userInfo.getPassword());
            query.executeUpdate();
        }
    }

    public ServingStation getUserByIdAndPassword(Integer userId, String oldPassword) throws Exception {
        String sql = "select ID, NAME  ,  PASSWORD,IS_FIRST_LOGIN from t_servicing_station where  id = :userId and password=:oldPassword ";
        try (Connection con = repairSql2oTemp.getSql2o().open()) {
            Query query = Sql2oConnection.createQuery(con,sql);
            query.addParameter("userId",userId);
            query.addParameter("oldPassword",oldPassword);
            return query.setAutoDeriveColumnNames(true).executeAndFetchFirst(ServingStation.class);
        }
    }
}
